package dao;

import java.sql.*;

/**
 * 数据库基础操作类
 */
public class BaseDao {
    private static final String URL = "jdbc:mysql://localhost:3306/mini12306";
    private static final String USER = "xixi";
    private static final String PASSWORD = "123456";

    // 初始化数据库
    public static void initDatabase() {
        try (Connection conn = getConnection()) {
            // 用户表
            String createUserTable = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INT PRIMARY KEY AUTO_INCREMENT," +
                    "账号 VARCHAR(50) UNIQUE NOT NULL," +
                    "密码 VARCHAR(100) NOT NULL," +
                    "姓名 VARCHAR(50) NOT NULL," +
                    "身份证号 VARCHAR(20) NOT NULL," +
                    "手机号 VARCHAR(20) NOT NULL," +
                    "银行卡号 VARCHAR(30)," +
                    "是否为管理员 BOOLEAN DEFAULT false," +
                    "年龄 INT" +
                    ")";
            executeUpdate(conn, createUserTable);

            // 列车表
            String createTrainTable = "CREATE TABLE IF NOT EXISTS trains (" +
                    "id INT PRIMARY KEY AUTO_INCREMENT," +
                    "车次 VARCHAR(20) NOT NULL," +
                    "出发地 VARCHAR(50) NOT NULL," +
                    "目的地 VARCHAR(50) NOT NULL," +
                    "出发时间 DATETIME NOT NULL," +
                    "到达时间 DATETIME NOT NULL," +
                    "总座位数 INT NOT NULL," +
                    "可用座位数 INT NOT NULL," +
                    "票价 DOUBLE NOT NULL" +
                    ")";
            executeUpdate(conn, createTrainTable);
            // 添加车厢座位信息
            String alterTable = "ALTER TABLE trains ADD COLUMN 车厢座位信息 TEXT";
            try {
                executeUpdate(conn, alterTable);
            } catch (SQLException e) {
            }

            // 车票表
            String createTicketTable = "CREATE TABLE IF NOT EXISTS tickets (" +
                    "id INT PRIMARY KEY AUTO_INCREMENT," +
                    "用户ID INT NOT NULL," +
                    "列车ID INT NOT NULL," +
                    "座位号 VARCHAR(10) NOT NULL," +
                    "订票时间 DATETIME NOT NULL," +
                    "票价 DOUBLE NOT NULL," +
                    "是否已取消 BOOLEAN DEFAULT false," +
                    "是否学生票 BOOLEAN DEFAULT false," + // 添加是否学生票字段
                    "FOREIGN KEY (用户ID) REFERENCES users(id)," +
                    "FOREIGN KEY (列车ID) REFERENCES trains(id)" +
                    ")";
            executeUpdate(conn, createTicketTable);

            System.out.println("数据库初始化完成");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    // 执行更新操作
    public static int executeUpdate(Connection conn, String sql) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            return stmt.executeUpdate(sql);
        }
    }

    //关闭资源
    public static void close(Connection conn, Statement stmt, ResultSet rs) {   //数据库连接对象、SQL语句执行对象、结果集对象
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}